|
工作中常遇到用oracle解析复杂类型的json（多层对象并且包含数组等）情况，本人在开发中，采用将复杂json逐层解析到表，随后再提取方法。
例：解析如下的json数据，某平台国内机票订购单据
{
"data": {
"AirPortFee": 50.000000,
"CompanyId": "C117507",
"CompanyName": "it测试专用公司",
"FlightInfo": [{
"AirlineName": "南方航空",
"ArrivalDate": "2018-09-05",
"Cabin": "2",
"Clazz": "J",
"DepartureDate": "2018-09-04",
"DestinationCityName": "上海(浦东)",
"FlightNo": "CZ3586",
"OriginCityName": "广州"
}],
"IssuteWay": 0,
"OpName": "陳智偉",
"OrderNo": "TB1800839048",
"OrderSource": 0,
"OrderStatus": "已处理",
"OrderType": 1,
"Passenger": [{
"PassengerAirPortFee": 50.0,
"PassengerCode": "P288725",
"PassengerName": "陈智伟",
"PassengerSalePrice": 3110.0,
"PassengerSaleServicePrice": 0.0,
"PassengerSaleTaxTwo": 10.0,
"PassengerType": "成人",
"TicketNo": "784-2977101969"
}],
"PriceTotal": 3170.000000,
"PurchaseChannelsType": 0,
"SalePrice": 3110.000000,
"SaleServicePrice": 0.000000,
"SaleTaxTwo": 10.000000,
"StartTime": "2018-07-21 10:43"
},
"password": "95aa19fb424fe74275f8608b90afbea344421346",
"timeStamp": "20180721111947904",
"msgType": "TBOrderInfo"
}
构建json解析结果表：
create table TB_JSON_DATA_DETAIL
(
id INTEGER not null,
json_id INTEGER,
path VARCHAR2(200),
kind VARCHAR2(5),
val VARCHAR2(2000),
parent_id INTEGER,
lvl INTEGER,
create_time DATE default sysdate,
item VARCHAR2(200),
seq_no INTEGER
);
|